// config/database.js
const mysql = require('mysql2/promise');
require('dotenv').config();

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'root',
  database: process.env.DB_NAME || 'workflow_db',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

const pool = mysql.createPool(dbConfig);

// 初始化数据库表
const initDatabase = async () => {
  try {
    const connection = await pool.getConnection();
    
    // 创建工作流表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS workflows (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        description TEXT,
        nodes_data JSON,
        edges_data JSON,
        created_by INT,
        is_active BOOLEAN DEFAULT true,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);
    
    // 创建工作流节点表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS workflow_nodes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        workflow_id INT,
        node_id VARCHAR(255) NOT NULL,
        node_type VARCHAR(100) NOT NULL,
        position_x FLOAT NOT NULL,
        position_y FLOAT NOT NULL,
        node_data JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
      )
    `);
    
    // 创建工作流边表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS workflow_edges (
        id INT AUTO_INCREMENT PRIMARY KEY,
        workflow_id INT,
        edge_id VARCHAR(255) NOT NULL,
        source_node_id VARCHAR(255) NOT NULL,
        target_node_id VARCHAR(255) NOT NULL,
        edge_data JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
      )
    `);
    
    // 创建工作流执行记录表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS workflow_executions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        workflow_id INT,
        status ENUM('pending', 'running', 'completed', 'failed') DEFAULT 'pending',
        input_data JSON,
        output_data JSON,
        started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        completed_at TIMESTAMP NULL,
        created_by INT,
        FOREIGN KEY (workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
      )
    `);
    
    connection.release();
    console.log('数据库表初始化完成');
  } catch (error) {
    console.error('数据库初始化失败:', error);
  }
};

module.exports = { pool, initDatabase };